﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Net.Mail;
using System.Web;
using OTS.BLL;
using System.Web.Configuration;

/// <summary>
/// Summary description for DeliveryDAO
/// </summary>
public partial class DeliveryDAO
{
    SqlHelp help = new SqlHelp();
	public DeliveryDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public DataTable GetContractNo(string strCondiction,string moreCondiction)
    {
        return help.GetDataTable("select distinct T.ContractNoMain from "
            + " M_SO_Trading T " + moreCondiction + " where T.TradingSoNo is not null and T.DeleteFlag=0 "
            + strCondiction + "  order by T.ContractNoMain desc ");
    }

    public DataTable[] GetSalesNameCustomerName()
    {
        return help.RunProcGetTables("GetSalesNameCustomerName", null);
    }

    public DataTable GetSalesName()
    {
        return help.GetDataTable("select distinct SC.SalesName from "
            + " M_SO_Trading T "
            + " join M_SalesCatalog SC on SC.ContractNoMain=T.ContractNoMain "
            + " where T.TradingSoNo is not null  and len(Rtrim(SC.SalesName))>0 "
            //+ " and (convert(float, isnull(TradingOrderQty,0))-convert(float, isnull(TradingShippedQty,0)))>0 "
            + "  order by SC.SalesName desc ");
    }
    public DataTable GetCustomerName()
    {
        return help.GetDataTable("select distinct C.CustomerName from "
            + " M_SO_Trading T "
            + " join M_Customer C on C.ContractNoMain=T.ContractNoMain "
            + " where T.TradingSoNo is not null  and len(Rtrim(C.CustomerName))>0 "
            //+ " and (convert(float, isnull(TradingOrderQty,0))-convert(float, isnull(TradingShippedQty,0)))>0 "
            + "  order by SC.CustomerName desc ");
    }

    public int TradingQTYForShippment(string rowID, string value)
    {
        return help.RunProcInt("TradingMemo", new SqlParameter[] { new SqlParameter("@value", value), new SqlParameter("@rowID", rowID) });
    }
    public int TradingDeliveryApply(DeliveryModel model)
    {
        int m = 0;
        try
        {
            if (model.rowIDValue.Length > 2)
            {
                string str = model.rowIDValue.Substring(0, model.rowIDValue.Length - 1);
                string[] all = str.Split(',');
                if (all.Length > 0)
                {
                    DataTable dt = help.RunDataTableProc("GetDeliveryApplyNo",
                        new SqlParameter[] { new SqlParameter("@rowIDs", "/" + model.rowIDValue.Replace(",", ",/")) });
                    foreach (string temp in all)
                    {
                        string[] aa = temp.Split('/');
                        if (aa.Length > 1 && aa[0].Length > 0 && aa[1].Length > 0)
                        {
                            DataRow[] row = dt.Select(string.Format("M_SO_TradingID={0}", aa[0]));
                            if (row.Length > 0)
                            {
                                m += help.RunProcInt("TradingMemo2",
                                    new SqlParameter[] { 
                                new SqlParameter("@CoCode", model.CoCode), 
                                new SqlParameter("@AddAuthor", model.UserCode), 
                                new SqlParameter("@value", aa[1]), 
                                new SqlParameter("@rowID", aa[0]) ,
                                new SqlParameter("@DeliveryApplyNo", row[0]["DeliveryApplyNo"].ToString()),
                                new SqlParameter("@Remark", model.remark),
                                new SqlParameter("@DeliveryApplyfileName", model.fileName)
                            });
                            }
                        }
                    }
                    #region 发邮件
                    if (m > 0)
                    {
                        EmailContent emailContent = new EmailContent();
                        emailContent.UserCode = model.UserCode;
                        emailContent.CoCode = model.CoCode;
                        emailContent.rowIDValue = "/" + model.rowIDValue.Replace(",", "/");
                        emailContent.flowType = 0;
                        emailContent.EmailTittl1 = "发货申请";
                        emailContent.EmailTittl2 = "申请日期：";
                        emailContent.EmailType = "发货申请";
                        emailContent.EmailTypeName = "发货申请";
                        emailContent.Content1_1 = "   About contract(ContractNo: ";
                        emailContent.Content1_2 = ") have finished applied.";
                        emailContent.Content2 = "Please check and approval to delivery in time!";
                        emailContent.Remark = model.remark;
                        emailContent.DeliveryNoDt = dt;

                        ApplySentEmail(emailContent);
                     }
                    #endregion
                }
            }
        }
        catch { }
        return m;
    }

    public int TradingQTYForShippment(DeliveryModel model)
    {
        string date = System.DateTime.Now.ToString("yyyy-MM-dd hh:mm");
        SqlParameter[] prams = new SqlParameter[]{
            new SqlParameter("@ContractNoMain", model.ContractNoMain),
            new SqlParameter("@CoCode", model.CoCode),
            new SqlParameter("@AddAuthor", model.UserCode)
        }; 
        return help.RunProcInt("TradingTradingSelectedQTYForShippmentToMemo", prams);
    }

    public DataTable GetSoNoCustomerSaleName(string contractNo)
    {
        return help.GetDataTable("select distinct T.ContractNoMain,T.ContractNoCust,T.TradingSoNo,"
            +"SC.SalesName,CU.CustomerName from M_SO_Trading T "
            + " left join M_SalesCatalog SC on T.ContractNoMain=SC.ContractNoMain"
            + " left join M_Customer CU on T.ContractNoMain=CU.ContractNoMain where T.DeleteFlag=0 and T.ContractNoMain='" + contractNo + "'");
    }

    public int Approval(DeliveryModel model)
    {
        EmailContent emailContent = new EmailContent();
        int m = 0;
        if (model.approvalFlag == 10)
        {
            SqlParameter[] prams = new SqlParameter[]{
                new SqlParameter("@UserCode", model.UserCode),
                new SqlParameter("@CoCode", model.CoCode),
                new SqlParameter("@M_SO_TradingID", model.M_SO_TradingID),
                new SqlParameter("@ContractNoMainSoNo", model.ContractNoMainSoNo.Replace(",",",/")),
                new SqlParameter("@flag", model.approvalFlag)
             };
            m = help.RunProcInt("TradingApplyCancel", prams);
            emailContent.DeliveryNoDt = help.RunDataTableProc("GetDeliveryNoDt",
                new SqlParameter[] { new SqlParameter("@ContractNoMainSoNo", model.ContractNoMainSoNo.Replace(",", ",/")) });
        }
        else
        {
            DataTable dt = help.GetDataTable(string.Format(@"select	DeliveryApplyNo, TradingSoNo, TradingSoLineNo, 
		        ContractNoMain, DeliveryApplyQty, TradingState, TradingStateValue, TradingDsce,backup1
                from	M_SO_Trading_DeliveryApply_Line
                where	'{0}' like '%/'+DeliveryApplyNo+'/-DANTID-/'+ContractNoMain+'/'+TradingSoNo+'/%'", 
                 model.ContractNoMainSoNo.Replace(",", ",/")));
            if (dt != null && dt.Rows.Count > 0)
            {
                string strCondiction = "/";
                foreach (DataRow row in dt.Rows)
                {
                    SqlParameter[] prams = new SqlParameter[]{
                        new SqlParameter("@UserCode", model.UserCode),
                        new SqlParameter("@CoCode", model.CoCode),
                        new SqlParameter("@DeliveryApplyNo", row["DeliveryApplyNo"].ToString()),
                        new SqlParameter("@ContractNoMain", row["ContractNoMain"].ToString()),
                        new SqlParameter("@TradingSoNo", row["TradingSoNo"].ToString()),
                        new SqlParameter("@TradingSoLineNo", row["TradingSoLineNo"].ToString()),
                        new SqlParameter("@flag", model.approvalFlag),
                        new SqlParameter("@strCondiction", strCondiction)
                        };
                    m += help.RunProcInt("TradingApproval", prams);
                }
            }
            emailContent.DeliveryNoDt = dt;
        }
        if (m > 0)
        {
            #region 发邮件
            SentEmailModel sentEmailModel = new SentEmailModel();
            sentEmailModel.UserCode = model.UserCode;
            if (model.approvalFlag == 1)
            {
                #region 审批通过
                emailContent.UserCode = model.UserCode;
                emailContent.CoCode = model.CoCode;
                emailContent.rowIDValue = "/" + model.rowIDValue.Replace(",", "/");
                emailContent.flowType = 1;
                emailContent.EmailTittl1 = "发货审批通过";
                emailContent.EmailTittl2 = "审批通过日期：";
                emailContent.EmailType = "发货审批通过";
                emailContent.EmailTypeName = "发货审批通过";
                emailContent.Content1_1 = "   About contract(ContractNo: ";
                emailContent.Content1_2 = ") have approved.";
                emailContent.Content2 = "Please check and delivery in time!";
                ApplySentEmail(emailContent);
                #endregion
            }
            else if (model.approvalFlag == 2)
            {
                #region 退回
                emailContent.UserCode = model.UserCode;
                emailContent.CoCode = model.CoCode;
                emailContent.rowIDValue = "/" + model.rowIDValue.Replace(",", "/");
                emailContent.flowType = 2;
                emailContent.EmailTittl1 = "发货申请审批_退回";
                emailContent.EmailTittl2 = "审批退回日期：";
                emailContent.EmailType = "发货审批退回";
                emailContent.EmailTypeName = "发货审批退回";
                emailContent.Content1_1 = "   About contract(ContractNo: ";
                emailContent.Content1_2 = ") have rejected.";
                emailContent.Content2 = "Please check and applied again in time!";
                ApplySentEmail(emailContent);
                #endregion
            }
            else if (model.approvalFlag == 3)
            {
                #region 发货审批取消
                emailContent.UserCode = model.UserCode;
                emailContent.CoCode = model.CoCode;
                emailContent.rowIDValue = "/" + model.rowIDValue.Replace(",", "/");
                emailContent.flowType = 1;
                emailContent.EmailTittl1 = "发货审批取消_Cancel";
                emailContent.EmailTittl2 = "审批取消日期：";
                emailContent.EmailType = "发货审批取消";
                emailContent.EmailTypeName = "发货审批取消";
                emailContent.Content1_1 = "   Approval of contract(ContractNo: ";
                emailContent.Content1_2 = ") have canceled!.";
                emailContent.Content2 = "Please check and apply again to delivery in time!";
                ApplySentEmail(emailContent);
                #endregion
            }
            else if (model.approvalFlag == 4)
            {
                #region 审批_保留
                emailContent.UserCode = model.UserCode;
                emailContent.CoCode = model.CoCode;
                emailContent.rowIDValue = "/" + model.rowIDValue.Replace(",", "/");
                emailContent.flowType = 4;
                emailContent.EmailTittl1 = "发货审批_保留";
                emailContent.EmailTittl2 = "审批保留日期：";
                emailContent.EmailType = "发货审批_保留";
                emailContent.EmailTypeName = "发货审批_保留";
                emailContent.Content1_1 = "   About contract(ContractNo: ";
                emailContent.Content1_2 = ") have approved.";
                emailContent.Content2 = "Please wait, It is hold!";
                ApplySentEmail(emailContent);
                #endregion
            }
            else if (model.approvalFlag == 10)
            {
                #region 发货申请取消
                emailContent.UserCode = model.UserCode;
                emailContent.CoCode = model.CoCode;
                emailContent.rowIDValue = "/" + model.rowIDValue.Replace(",", "/");
                emailContent.flowType = 0;
                emailContent.EmailTittl1 = "发货申请取消";
                emailContent.EmailTittl2 = "申请取消日期：";
                emailContent.EmailType = "发货申请取消";
                emailContent.EmailTypeName = "发货申请取消";
                emailContent.Content1_1 = "   Applied of contract(ContractNo: ";
                emailContent.Content1_2 = ") have canceled.";
                emailContent.Content2 = "Please check and neglect approval!";
                ApplySentEmail(emailContent);
                #endregion
            }
            #endregion
        }
        return m;
    }

    public int ApprovalRemarkUpdateDAO(string strID, string strValue, string userCode, string coCode, int n)
    {
        SqlParameter[] prams = new SqlParameter[]{
            new SqlParameter("@UserCode", userCode),
            new SqlParameter("@CoCode", coCode),
            new SqlParameter("@M_SO_TradingID", strID),
            new SqlParameter("@flag", n),
            new SqlParameter("@Remark", strValue)
        };
        return help.RunProcInt("ApprovalRemarkUpdate", prams);
    }
        
    public DataTable[] GetddlForApplyListQuery()
    {
        return help.RunProcGetTables("GetddlForApplyListQuery", null);
    }
    public DataTable[] GetddlForApply()
    {
        return help.RunProcGetTables("GetddlForApply", null);
    }

    public DataTable[] GetEmailForDeliveryApply(EmailContent emailContent)
    {
        return help.RunProcGetTables("GetEmailForDeliveryApply",
            new SqlParameter[]{
                new SqlParameter("@UserCode",emailContent.UserCode),
                new SqlParameter("@CoCode",emailContent.CoCode),
                new SqlParameter("@strIDs",emailContent.rowIDValue),
                new SqlParameter("@FlowType",emailContent.flowType)
            });
    }

    //申请发邮件
    public void ApplySentEmail(EmailContent emailContent)
    {
        int result = 0;
        DataTable[] dt = GetEmailForDeliveryApply(emailContent);
        if (dt != null && dt.Length > 1 && dt[0].Rows.Count > 0 & dt[1].Rows.Count > 0)
        {
            MailMessage mailObj = new MailMessage();
            string StrFrom = WebConfigurationManager.AppSettings["publicEmail"];
            string StrPwd = WebConfigurationManager.AppSettings["publicEmailPWD"];
            //发件人名
            string StrFromPerson = string.Empty;
            //收件人
            string StrToPerson = string.Empty;
            string date = System.DateTime.Now.ToString("yyyy-MM-dd");
            string time = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string contractNo = string.Empty;
            string soNo = string.Empty;
            string remark = string.Empty;
            string deliveryNo = string.Empty;

            StrFromPerson = dt[0].Rows[0]["UserShowName"].ToString();
            // 添加发件人
            mailObj.From = new MailAddress(StrFrom);
            mailObj.CC.Add(dt[0].Rows[0]["Email"].ToString());

            for (int i = 0; i < dt[1].Rows.Count; i++)
            {
                contractNo = dt[1].Rows[i]["ContractNoMain"].ToString();
                result = OtherSoNoPosition(i, dt[1]);

                //添加邮件接收人
                if (emailContent.flowType == 0 || emailContent.flowType == 1)
                {
                    if (dt.Length > 2 && dt[2] != null && dt[2].Rows.Count > 0)
                    {
                        foreach (DataRow row2 in dt[2].Rows)
                        {
                            mailObj.To.Add(row2["Email"].ToString());
                            StrToPerson += row2["UserShowName"].ToString() + " ";
                        }
                        for (int k = i; k <= result; k++)
                        {
                            mailObj.CC.Add(dt[1].Rows[k]["Email"].ToString());
                            //StrToPerson += dt[1].Rows[k]["UserShowName"].ToString() + " ";
                        }
                    }
                }
                else
                {
                    for (int k = i; k <= result; k++)
                    {
                        mailObj.To.Add(dt[1].Rows[k]["Email"].ToString());
                        StrToPerson += dt[1].Rows[k]["UserShowName"].ToString() + " ";
                    }
                }

                if (WebConfigurationManager.AppSettings["publicEmailSent"].Equals("00000"))
                { mailObj.CC.Add("tsu@shinfomelody.com"); }
                else if (WebConfigurationManager.AppSettings["publicEmailSent"].Equals("1"))
                {
                    mailObj.CC.Add("Ocean.He@itt.com");
                    mailObj.CC.Add("tsu@shinfomelody.com");
                }
                else if (WebConfigurationManager.AppSettings["publicEmailSent"].Equals("99999"))
                { mailObj.CC.Add("Ocean.He@itt.com"); }

                //StrToPerson = row["UserName"].ToString();
                contractNo = dt[1].Rows[result]["ContractNoMain"].ToString();
                soNo = dt[1].Rows[result]["TradingSoNo"].ToString();
                remark = dt[1].Rows[result]["TradingDsce"].ToString();
                if (emailContent.flowType == 0)
                { remark = emailContent.Remark; }
                if (emailContent.DeliveryNoDt != null && emailContent.DeliveryNoDt.Rows.Count > 0)
                {
                    DataRow[] rows = emailContent.DeliveryNoDt.Select(string.Format("ContractNoMain='{0}' and TradingSoNo='{1}'", 
                        contractNo, soNo));
                    if (rows.Length > 0)
                    {
                        deliveryNo = rows[0]["DeliveryApplyNo"].ToString();
                    }
                }
                // 发邮件
                #region
                SentEmailModel sentEmailModel = new SentEmailModel();
                sentEmailModel.ContractNoMain = contractNo;
                sentEmailModel.UserCode = emailContent.UserCode;
                sentEmailModel.EmailTittl = emailContent.EmailTittl1
                    + string.Format("(ContractNo: {0}, SoNo: {1}, DeliveryApplyNo: {2} ", contractNo, soNo, deliveryNo)
                    + emailContent.EmailTittl2 + date + ")";
                sentEmailModel.EmailBody = string.Format("<div><div style=\"height:15px;\"></div>"
                        + "<div>Dear {0}</div> <br /><div>"
                           + " <table cellpadding=\"0\" cellspacing=\"0\">"
                          + "  <tr><td style=\"height:22px; text-align:left;\">{6}</td></tr>"
                            + "<tr><td style=\"height:22px; text-align:left;\">{5}</td></tr>"
                          + "  <tr><td style=\"height:22px; text-align:left;\">备注：{7}</td></tr>"
                          + "  <tr><td style=\"height:22px; text-align:left;\"></td></tr>"
                          + "  <tr><td style=\"height:22px; text-align:left;\">Best Regards.</td></tr>"
                          + "  <tr><td style=\"height:22px; text-align:left;\"></td></tr>"
                           + " <tr><td style=\"height:22px; text-align:left;\">{1}</td></tr>"
                           + " </table></div></div>",
                   StrToPerson, StrFromPerson, emailContent.Content1_1, contractNo, emailContent.Content1_2,
                   emailContent.Content2, sentEmailModel.EmailTitle, remark);
                #endregion

                // 邮件内容
                mailObj.Subject = sentEmailModel.EmailTittl;
                mailObj.Body = sentEmailModel.EmailBody;
                try
                {
                    SentEmailBLL.SentEmail(mailObj, StrFrom, StrPwd);

                    #region Email_Record
                    sentEmailModel.ContractNoCust = contractNo;
                    sentEmailModel.EmailContent = sentEmailModel.EmailBody;
                    sentEmailModel.EmailSentDate = time;
                    sentEmailModel.EmailTitle = sentEmailModel.EmailTittl;
                    sentEmailModel.EmailType = emailContent.EmailType;
                    sentEmailModel.EmailTypeName = emailContent.EmailTypeName;
                    sentEmailModel.UserCode = emailContent.UserCode;
                    sentEmailModel.StrToPerson = StrToPerson;
                    sentEmailModel.Remark = remark;
                    sentEmailModel.DeliveryApplyNo = deliveryNo;

                    SentEmailBLL.EmailRecord(sentEmailModel);
                    #endregion
                }
                catch (Exception ex)
                {
                    Log.WritLog("ApplySentEmail:", ex.ToString());
                }

                i = result;
            }
        }
    }

    public int OtherSoNoPosition(int m, DataTable dt)
    {
        int n = m - 1;
        string sono = dt.Rows[m]["TradingSoNo"].ToString();
        for (int i = m; i < dt.Rows.Count; i++)
        {
            if (!sono.Equals(dt.Rows[i]["TradingSoNo"]))
                break;
            n++;
        }

        return n;
    }

    public double GetDeliverySelectAmount(DeliveryModel model)
    {
        double applyAmount = 0;
        DataTable dt = help.RunDataTableProc("GetDeliverySelectAmount",
            new SqlParameter[]{
                new SqlParameter("@ContractNoMain",model.ContractNoMain),
                new SqlParameter("@SoNo",model.SoNo)
            });
        if (dt != null && dt.Rows.Count > 0)
        {
            try { applyAmount = Convert.ToDouble(dt.Rows[0]["TradingApprovingAmount"]); }
            catch { }
        }

        return applyAmount;
    }
}